package com.runnirr.doodles.Database;


import static android.provider.BaseColumns._ID;

import java.util.ArrayList;
import java.util.List;

import com.runnirr.doodles.Helpers.DoodleData;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataManagementHelper extends SQLiteOpenHelper {

	private static final int DB_VERSION = 3;
	private static final String DB_NAME = "DOODLE_VIEWER.db";
	private static final String DB_DATA_TABLE_NAME = "DOODLE_DATA_TABLE";
	private static final String DB_IMAGE_TABLE_NAME = "DOODLE_IMAGE_TABLE";



	public DataManagementHelper(Context context){
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DB_COMMANDS.DB_CREATE_DATA_TABLE);
		db.execSQL(DB_COMMANDS.DB_CREATE_IMAGE_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
		if (oldVer <= 1){
			updateDbFromOne(db);
			oldVer++;
		}
		if (oldVer <= 2){
			updateDbFromTwo(db);
			oldVer++;
		}
	}

	/**
	 * Retrieve doodles from storage
	 * @param year - year the doodles were displayed
	 * @param month - month the doodles were displayed
	 * @return List of doodles for the given year/month
	 */
	public List<DoodleData> selectDoodlesFromStorage(Integer year, Integer month){
		List<DoodleData> doodles = new ArrayList<DoodleData>();

		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		try{
			cursor = db.query(
					DB_DATA_TABLE_NAME, 
					null,
					DB_COLUMNS.DOODLE_YEAR + "=? " +
							"AND " +  DB_COLUMNS.DOODLE_MONTH + "=?",  
							new String[]{year.toString(), month.toString()}, 
							null, 
							null, 
							_ID + " DESC");
		}catch(Exception e){
			db.close();
			Log.e("Database error", e.getLocalizedMessage());
			return doodles;
		}


		final int year_ind = cursor.getColumnIndex(DB_COLUMNS.DOODLE_YEAR);
		final int month_ind = cursor.getColumnIndex(DB_COLUMNS.DOODLE_MONTH);
		final int name_ind = cursor.getColumnIndex(DB_COLUMNS.DOODLE_NAME);
		final int title_ind = cursor.getColumnIndex(DB_COLUMNS.DOODLE_TITLE);
		final int desc_ind = cursor.getColumnIndex(DB_COLUMNS.DOODLE_DESCRIPTION);
		final int image_ind = cursor.getColumnIndex(DB_COLUMNS.DOODLE_IMAGE_LINK);
		final int date_ind = cursor.getColumnIndex(DB_COLUMNS.DOODLE_DATE);
		final int id_ind = cursor.getColumnIndex(DB_COLUMNS.DOODLE_ID);

		while(cursor.moveToNext()){
			DoodleData dd = new DoodleData(
					cursor.getInt(year_ind),
					cursor.getInt(month_ind),
					cursor.getString(name_ind),
					cursor.getString(title_ind),
					cursor.getString(desc_ind),
					cursor.getString(image_ind),
					cursor.getLong(date_ind),
					cursor.getString(id_ind));
			doodles.add(dd);
		}
		db.close();
		return doodles;
	}

	/**
	 * Retrieve doodle image based on doodle id
	 * @param id (string)
	 * @return byte[] for the image
	 */
	public byte[] selectDoodleImageFromStorage(String id){
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(DB_IMAGE_TABLE_NAME, 
				new String[]{DB_COLUMNS.DOODLE_IMAGE}, 
				DB_COLUMNS.DOODLE_ID + "=?", 
				new String[]{id}, 
				null, null, null);
		
		if(cursor.moveToFirst()){
			return cursor.getBlob(cursor.getColumnIndex(DB_COLUMNS.DOODLE_IMAGE));
		}
		return new byte[0];
	}

	/**
	 * Store a doodle locally
	 * @param data DoodleData object
	 */
	public void insertDoodleToStorage(DoodleData data){
		ContentValues toInsert = getContentValuesFromDoodleData(data);
		if (toInsert != null){
			SQLiteDatabase db = getWritableDatabase();
			db.insertWithOnConflict(DB_DATA_TABLE_NAME, null, 
					toInsert, SQLiteDatabase.CONFLICT_IGNORE);
			db.close();
		}
	}

	/**
	 * Store an image locally
	 * @param image byte[] for the bitmap
	 * @param id Id of the doodle the image belongs to
	 */
	public void insertImageToStorage(byte[] image, String id){
		ContentValues toInsert = getContentValuesForImage(image, id);
		if (toInsert != null){
			SQLiteDatabase db = getWritableDatabase();
			db.insertWithOnConflict(DB_IMAGE_TABLE_NAME, null, 
					toInsert , SQLiteDatabase.CONFLICT_IGNORE);
			db.close();
		}
	}
	
	/**
	 * Remove all local data for the app
	 */
	public void deleteAllData(){
		SQLiteDatabase db = getWritableDatabase();
		db.delete(DB_DATA_TABLE_NAME, null, null);
		db.delete(DB_IMAGE_TABLE_NAME, null, null);
		db.close();
	}

	/**
	 * Convert a DoodleData into ConcentValues for database storage
	 * @param dd DoodleData
	 * @return ContentValues
	 */
	private ContentValues getContentValuesFromDoodleData(DoodleData dd){
		ContentValues cv = null;
		if (dd.ID != null && dd.ID != "" && dd.year != null && 
				dd.month != null && dd.title != null &&
				dd.name != null && dd.image != null && dd.url != null){
			cv = new ContentValues();

			cv.put(DB_COLUMNS.DOODLE_ID, dd.ID);
			cv.put(DB_COLUMNS.DOODLE_YEAR, dd.year);
			cv.put(DB_COLUMNS.DOODLE_MONTH, dd.month);
			cv.put(DB_COLUMNS.DOODLE_TITLE, dd.title);
			cv.put(DB_COLUMNS.DOODLE_DATE, dd.startTime);
			cv.put(DB_COLUMNS.DOODLE_NAME, dd.name);
			cv.put(DB_COLUMNS.DOODLE_IMAGE_LINK, dd.image);
			cv.put(DB_COLUMNS.DOODLE_DESCRIPTION, dd.text);
			cv.put(DB_COLUMNS.DOODLE_URL, dd.url);
		}
		return cv;
	}

	/**
	 * Get content values of an image and doodle id
	 * @param image byte array of the bitmap
	 * @param id doodle id
	 * @return ContentValues
	 */
	private ContentValues getContentValuesForImage(byte[] image, String id){
		ContentValues cv = null;
		if (image.length > 0 && id != ""){
			cv = new ContentValues();

			cv.put(DB_COLUMNS.DOODLE_ID, id);
			cv.put(DB_COLUMNS.DOODLE_IMAGE, image);

		}
		return cv;
	}

	/**
	 * Update from v1 to v2
	 * @param db Database
	 */
	private void updateDbFromOne(SQLiteDatabase db){
		db.delete(DB_DATA_TABLE_NAME, DB_COLUMNS.DOODLE_DATE + " IS NULL", null);
	}

	/**
	 * Update from v2 to v3
	 * @param db Database
	 */
	private void updateDbFromTwo(SQLiteDatabase db){
		db.execSQL(DB_COMMANDS.DB_CREATE_IMAGE_TABLE);
	}


	/**
	 * Private class for storing query string for the database
	 */
	private final class DB_COMMANDS{
		public static final String DB_CREATE_DATA_TABLE = 
				"CREATE TABLE " + DB_DATA_TABLE_NAME + " (" + 
						_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
						DB_COLUMNS.DOODLE_ID + " TEXT, " +
						DB_COLUMNS.DOODLE_YEAR + " INTEGER, " +
						DB_COLUMNS.DOODLE_MONTH + " INTEGER, " +
						DB_COLUMNS.DOODLE_TITLE + " TEXT, " +
						DB_COLUMNS.DOODLE_NAME + " TEXT, " +
						DB_COLUMNS.DOODLE_DATE + " INTEGER, " +
						DB_COLUMNS.DOODLE_IMAGE_LINK + " TEXT, " +
						DB_COLUMNS.DOODLE_DESCRIPTION + " TEXT, " +
						DB_COLUMNS.DOODLE_URL + " TEXT, " +
						"UNIQUE (" + DB_COLUMNS.DOODLE_ID + ")" + 
						");";

		public static final String DB_CREATE_IMAGE_TABLE = 
				"CREATE TABLE " + DB_IMAGE_TABLE_NAME + " (" +
						_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
						DB_COLUMNS.DOODLE_IMAGE + " BLOB, " +
						DB_COLUMNS.DOODLE_ID + " TEXT, " +
						"UNIQUE (" + DB_COLUMNS.DOODLE_ID + ")" +
						");";
		
	}

}
